# -*- coding:utf-8 -*-
import pymysql

class stuinfo:
    """用以查询，删除，添加学生信息"""
    #_db = null
    #_cursor = null
    def __init__(self):
        #获取数据库连接
        self._db = pymysql.connect(host="localhost",user="root",password="",db="studb",charset="utf8")
        self._cursor = self._db.cursor()

    def _exesql(self,sql):
        try:
            # 执行sql操作
            self._cursor.execute(sql)
            # 事务提交
            self._db.commit()
            print("The operation was executed successfully!")
        except Exception as err:
            # 事务回滚
            self._db.rollback()
            print("SQL执行错误！原因：", err)

    def findAll(self):
        """查询学生信息"""
        findsql = "select * from stu"
        self._exesql(findsql)
        dlist = self._cursor.fetchall()
        if len(dlist) == 0:
            print("========== 没有学员信息可以输出！=============")
            return
        print("|{0:^5}| {1:^10}| {2:^5}| {3:^10}|".format("id", "name", "age", "classid"))
        print("-" * 40)
        for data in dlist:
            #print(data[0])
            print("|{0:^5}| {1:^10}| {2:^5}| {3:^10}|".format(data[0],data[1],data[2],data[3]))

    def delete(self,id):
        """删除指定学生信息"""
        delsql = "delete from stu where id=%d"%(id)
        self._exesql(delsql)


    def insert(self,data):
        """插入学生信息"""
        inssql = "insert into stu(name,age,classid) values ('%s','%d','%s')"%(data)
        self._exesql(inssql)

    def __del__(self):
        #关闭数据库连接
        self._db.close()

    def main(self):
        "在第一周示例基础上完成的修改"
        while True:
            # 输出初始界面
            print("=" * 12, "学员管理系统", "=" * 14)
            print("{0:1} {1:13} {2:15}".format(" ", "1. 查看学员信息", "2. 添加学员信息"))
            print("{0:1} {1:13} {2:15}".format(" ", "3. 删除学员信息", "4. 退出系统"))
            print("=" * 40)
            key = input("请输入对应的选择：")
            # 根据键盘值，判断并执行对应的操作
            if key == "1":
                print("=" * 12, "学员信息浏览", "=" * 14)
                self.findAll()
                input("按回车键继续：")
            elif key == "2":
                print("=" * 12, "学员信息添加", "=" * 14)
                data = []
                data.append(input("请输入要添加的姓名："))
                data.append(int(input("请输入要添加的年龄：")))
                data.append(input("请输入要添加的班级号："))
                #data = tuple(temp)
                self.insert(tuple(data))
                input("按回车键继续：")
            elif key == "3":
                print("=" * 12, "学员信息删除", "=" * 14)
                self.findAll()
                sid = input("请输入你要删除的信息id号：")
                self.delete(int(sid))
                self.findAll()
                input("按回车键继续：")
            elif key == "4":
                print("=" * 12, "再见", "=" * 14)
                break
            else:
                print("======== 无效的键盘输入！ ==========")

if __name__ == '__main__':
    test = stuinfo()
    test.main()